<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use function EasyWeChat\Kernel\Support\current_url;

class ToolsService extends BaseService
{
    /**
     * 默认的请求数据长度
     */
    const LIMIT = 30;

    /**
     * 涉及系统
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B001(array $parameters = []) : array
    {
        if (!isset($parameters['page']) || $parameters['page']) {
            $parameters['page'] = 1;
        }

        if (!isset($parameters['name']) || empty($parameters['name'])) {
            $sql = "select * from format_doc where category_id = 3 and is_deleted = 0 order by tag_prefix limit ".self::LIMIT;
        } else {
            $sql = "select * from format_doc where category_id = 3 and is_deleted = 0 and name like '%".$parameters['name']."%' order by tag_prefix limit ". ($parameters['page'] -1) * self::LIMIT.','.$parameters['page'] * self::LIMIT;
        }

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 需求类型
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B002(array $parameters = []) : array
    {
        $sql = "select id,name from s_original_formatdoc_category order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 需求状态
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B003(array $parameters = []) : array
    {
        $sql = "select id,name from s_original_format_doc_status order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 需求完成时效
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B004(array $parameters = []) : array
    {
        if (!isset($parameters['table_index']) || empty($parameters['table_index'])) {
            return [];
        }

        $sql = "select id,name from s_original_formatdoc_standard_table where table_index = '".$parameters['table_index']. "' order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 系统汇总-需求处室
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B005(array $parameters = []) : array
    {
        $sql = "SELECT DISTINCT req_office_id as id, req_office as name FROM assessment_req_dev_spilt ars where req_office <> '' and req_office_id <> '' ";

        $data = DB::connection('bpm_rs')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 申请人
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B008(array $parameters = []) : array
    {
        $sql = "select id,name from person where unit_id is not null and is_deleted =0 order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 涉及系统
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B009(array $parameters = []) : array
    {
        $sql = "select distinct plate_id from plate_unit order by plate_id";

        $data = DB::connection('pms')
            ->select($sql);
    }

    /**
     * IT申请人信息
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B010(array $parameters = []) : array
    {
        $sql = "select office_phone,mobile from person where id in (SELECT person_id FROM user where username = '".$parameters['username']."' ) order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            $data = array_map(function ($item){
                $result = [];

                if (!empty($item['office_phone'])) {
                    $result['office_phone'] = $item['office_phone'];
                }

                if (!empty($item['mobile'])) {
                    $result['mobile'] = $item['mobile'];
                }

                return $result;
            },$data);

            $data = array_filter($data,function ($item){
                return !empty($item);
            });

            return $data;
        }

        return [];
    }

    public function R_assessment_B011(array $parameters = [])
    {

    }

    public function R_assessment_B014()
    {

    }

    /**
     * 涉及板块
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B015(array $parameters = []) : array
    {
        if ( (isset($parameters['dev_office_id']) || empty($parameters['dev_office_id']) )|| (isset($parameters['userName']) || empty($parameters['userName'])) ) {
            $sql = "select distinct plate_id from plate_unit where unit_id = '".$parameters['dev_office_id']."' order by plate_id";

            $data = DB::connection('pms')
                ->select($sql);

            if (!empty($data)) {
                $data = json_decode(json_encode($data),true);

                $result = [];

                foreach ($data as $value) {
                    $result[] = $value['plate_id'];
                }

                if (!empty($result)) {
                    $sql = "select id,name from s_system_formatdoc_standard_table_3 where id in (".implode(",",$result).")" ;

                    $data = DB::connection('pms')
                        ->select($sql);

                    if (!empty($data)) {
                        $data = json_decode(json_encode($data),true);

                        return  $data;
                    }
                }
            }
        }

        return [];
    }

    /**
     * 涉及系统
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B016(array $parameters = []) : array
    {
        if (!isset($parameters['plate_id']) || empty($parameters['plate_id'])) {
            return [];
        }

        $sql = "select distinct uid,NAME as name FROM format_doc WHERE is_deleted=0 AND standard_column_5 !=4 AND standard_column_3= '".$parameters['plate_id']."'";

        $data = DB::connection('pms')
            ->select($sql);

        $resultSql = "select pmsUserId as id,CnName as name from bpm_orguserlist where pmsUserId in ";

        $resultArray = [];

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            foreach ($data as $value) {
                if ($value['name'] == '新建系统'){
                    $sql = "select distinct u.id,p.name from unit un left join person p on p.unit_id = un.id left join user u on u.person_id = p.id where p.is_deleted = 0 and (un.id = 375 or un.parent_id = 375)";

                    $reqResult = DB::connection('pms')
                        ->select($sql);

                    $devResult = DB::connection('pms')
                        ->select($sql);

                    if (!empty($reqResult)) {
                        $reqResult = json_decode(json_encode($reqResult),true);
                    }

                    if (!empty($devResult)) {
                        $devResult = json_decode(json_encode($devResult),true);
                    }

                    $resultArray[] = [
                        'reqData' => $reqResult,
                        'devData' => $devResult
                    ];
                } else {
                    $reqSql = "select user_id from r_system_person_role where doc_uid = '".$value['uid']."' and (systemrole=3 or systemrole=4)";

                    $reqSqlResult = DB::connection('pms')
                        ->select($reqSql);

                    if (!empty($reqSqlResult)) {
                        $reqSqlResult = json_decode(json_encode($reqSqlResult), true);

                        $reqUserIdArray = [];

                        foreach ($reqSqlResult as $v) {
                            $reqUserIdArray[] = $v['user_id'];
                        }

                        $reqResultSql = $resultSql . " (".implode(",",$reqUserIdArray).")";

                        $reqResult = DB::connection('bpm_rs')
                            ->select($reqResultSql);
                    }

                    $devSql = "select user_id from r_system_person_role where doc_uid = '".$value['uid']."' and (systemrole=1 or systemrole=2)";

                    $devSqlResult = DB::connection('pms')
                        ->select($devSql);

                    if (!empty($devSqlResult)) {
                        $devSqlResult = json_decode(json_encode($devSqlResult), true);

                        $devUserIdArray = [];

                        foreach ($devSqlResult as $v) {
                            $devUserIdArray[] = $v['user_id'];
                        }

                        $devResultSql = $resultSql . " (".implode(",",$devUserIdArray).")";

                        $devResult = DB::connection('bpm_rs')
                            ->select($devResultSql);
                    }

                    $reqResult = $reqResult ?? [];
                    $devResult = $devResult ?? [];

                    if (!empty($reqResult)) {
                        $reqResult = json_decode(json_encode($reqResult),true);
                    }

                    if (!empty($devResult ?? [])) {
                        $devResult = json_decode(json_encode($devResult),true);
                    }

                    $resultArray[] = [
                        'reqData' => $reqResult,
                        'devData' => $devResult
                    ];
                }
            }
        }

        return $resultArray;
    }

    /**
     * 获取开发处长
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B017(array $parameters = []) : array
    {
        $sql = "select xmldata from bpm_maindata where WF_OrUnid = '".$parameters['WF_OrUnid']."' ";

        $docData = DB::connection('bpm_rs')
            ->select($sql);

        if (!empty($docData)) {
            $docData = json_decode(json_encode($docData),true);

            $docData = current($docData);
        }

        $exitHardReturnSql = "select * from assessment_req_dev_spilt where instanceId = '".$parameters['WF_OrUnid']."' and hard_return ='true'";

        if ($parameters['hard_return'] == 'true') {
            $data = DB::connection('bpm_rs')
                ->select($exitHardReturnSql);

            if (empty($data)) {
                $sql = "select distinct ul.Userid from assessment_req_dev_spilt ads left join bpm_orguserlist ul on ul.pmsUserId=ads.req_name_id where ads.instanceId='".$parameters["WF_OrUnid"]."' and ads.hard_return='true' and (ads.is_return='是' or ads.function_name='')";

                $result = DB::connection('bpm_rs')
                    ->select($sql);
            }
        }
    }

    /**
     * 获取需求处长
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B026(array $parameters = []) : array
    {
        $sql = "select u.username from person p  LEFT JOIN USER u ON p.id = u.id LEFT JOIN r_person_post rpp ON rpp.`person_id` = p.`id` LEFT JOIN post po ON po.id = rpp.`post_id` LEFT JOIN unit un ON un.`id` = po.`unit_id` where un.`name`='项目管理团队' AND po.`name`='处长' AND po.`is_leader`=1";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 生成需求编号
     * @param array $parameters
     * @return string
     */
    public function R_assessment_B025(array $parameters = []) : string
    {
        $sql = "select o.no from original_format_doc o where o.create_date = (SELECT MAX(o1.create_date) FROM original_format_doc o1)";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            $number = current($data)['no'] ?? '';

            if (strlen($number) == 14) {
                $newNumber = substr($number,9,strlen($number));

                $newNumber = 'RA-'.date("Y").'Z'.(intval($newNumber) +1);

            } else {
                $newNumber = 'RA-'.date("Y").'Z000001';
            }

            return $newNumber;
        }

        return '';
    }


    public function R_assessment_B024(array $parameters = [])
    {

    }

    /**
     * 获取自主开发处室
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B023(array $parameters = []) : array
    {
        $sql = "select ars.req_office_id from assessment_req_dev_spilt ars where ars.instanceId='".$parameters['WF_OrUnid']."'";

        $data = DB::connection('bpm_rs')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            $reqOfficeIdArray = [];

            foreach ($data as $value) {
                $reqOfficeIdArray[] = $value['req_office_id'];
            }

            $resultSql = "select u.username from person p left join user u on p.id=u.id LEFT JOIN r_person_post rpp ON rpp.`person_id`=p.id LEFT JOIN post po ON po.`unit_id`=p.`unit_id` where rpp.`post_id`=po.`id` and p.`unit_id` in (".implode(",",$reqOfficeIdArray).") and po.`is_leader`=1";

            $result = DB::connection('pms')
                ->select($resultSql);

            if (!empty($result)) {
                $result = json_decode(json_encode($result),true);

                return $result;
            }
        }

        return [];
    }

    /**
     * 获取自主开发处室
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B022(array $parameters = []) : array
    {
        $sql = "select id,'信息技术部' name from unit where name = '信息技术部'";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 获取需求优先级
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B021(array $parameters = []) : array
    {
        $sql = "select id,name from s_original_format_doc_priority order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    /**
     * 获取数据库服务器
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B020(array $parameters = []) : array
    {
        $sql = "select id,name from s_original_format_hardware_server order by id";


        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    public function R_assessment_B019()
    {

    }


    //今天

    public function R_assessment_B027()
    {

    }

    /**
     *
     */
    public function R_assessment_E014()
    {

    }

    public function R_assessment_B032()
    {

    }

    public function R_assessment_B029(array $parameters) : string
    {
        $sql = "select SUM(ar.Expected_it_input) AS availableBudget from assessment_result ar LEFT JOIN original_format_doc o ON o.uid = ar.original_docUid where o.annual_name = '".$parameters['name']."'";

        $data = DB::connection('pms')
            ->select($sql);
        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return strval(current($data)['availableBudget']) ?? '0';
        }

        return "0";
    }

    /**
     * 富文本转码
     * 去除reqGround中多余的html标签
     * @param string $content
     * @return string
     */
    public function R_assessment_P001(string $content) : string
    {
        return strip_tags($content);
    }

    /**
     * 号码验证
     * @param array $parameters
     * @return array
     */
    public function R_assessment_P002(array $parameters) : array
    {
        $sql = "select office_phone,mobile from person where id in (SELECT person_id FROM `user` where username = '".$parameters['user_id']."')";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            $result = [];

            foreach ($data as $value) {
                if (!empty($value['office_phone']) && !empty($value['mobile'])) {
                    $result[] = $value['office_phone'];
                } else if (!empty($value['mobile'])) {
                    $result[] = $value['mobile'];
                }
            }

            return $result;
        }

        return [];
    }

    /**
     * 获取流程视图人员
     * @param array $parameters
     * @return array
     */
    public function R_assessment_P003(array $parameters) : array
    {
        $sql = "select dev_name,req_name from assessment_dev_split where create_name= '".$parameters['user_id']."'";

        $data = DB::connection('bpm_rs')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            $result = [];

            foreach ($data as $value) {
                $result[] = $value['dev_name'];
                $result[] = $value['req_name'];
            }

            return $result;
        }

        return [];
    }

    /**
     * 获取当前人员所在的单位
     * @param array $parameters
     * @return array
     */
    public function R_assessment_P004(array $parameters) :array
    {
        $sql = "SELECT d.unitId, d.FolderName FROM bpm_orguserdeptmap ud LEFT JOIN bpm_orgdeptlist d ON d.Folderid=ud.Folderid WHERE ud.Userid='".$parameters['user_id']."'";

        $data = DB::connection('bpm_rs')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return current($data);
        }

        return [];
    }

    /**
     * 获取项目类型
     * @param array $parameters
     * @return array
     */
    public function R_assessment_B037(array $parameters = []) : array
    {
        $sql = "select id,name from s_project_standard_table_1 order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];

    }

    public function R_assessment_B038(array $parameters = []) : array
    {
        $sql = "select id,name from s_project_standard_table_2 order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];

    }

    public function R_assessment_B039(array $parameters = []) : array
    {
        $sql = "select id,name from s_project_standard_table_6 order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    public function R_assessment_B040(array $parameters = []) : array
    {
        $sql = "select id,name from s_project_standard_table_4 order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    public function R_assessment_B041(array $parameters = []) : array
    {
        $sql = "select id,name from s_project_standard_table_5 order by id";

        $data = DB::connection('pms')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            return $data;
        }

        return [];
    }

    public function R_assessment_B042(array $parameters = []) : bool
    {
        $sql = "SELECT DISTINCT ads.req_name,ads.dev_name FROM `assessment_req_dev_spilt` ads where ads.instanceId='".$parameters['WF_OrUnid']."' and ads.plate_id='".$parameters['plateId']."' limit 1";

        $data = DB::connection('bpm_rs')
            ->select($sql);

        if (!empty($data)) {
            $data = json_decode(json_encode($data),true);

            $data = current($data);

            if (empty($data['req_name']) || empty($data['dev_name'])) {
                return false;
            }

            return true;
        }

        return false;
    }

    //R_assessment_P004,R_assessment_P003,R_assessment_P002,R_assessment_P001,R_assessment_P002
}
